package hotel.discard;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import hotel.common.JDBC;
import hotel.modal.Room;

public class RoomSql {
	protected static Connection conn = null;
	protected static Statement stmt = null;
	protected static ResultSet rs = null;

	// 获取所有房间信息
	public static ArrayList<Room> getAll() {
		String sql;
		ArrayList<Room> data = new ArrayList<Room>();
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT RoomNo, `Status`, PhoneNum, Position, Principal,"+
				"TypeID, TypeName, HourRoom, BedNum, Deposit, Price FROM "+
				"room_info ,room_type WHERE room_info.Type = room_type.TypeID order by RoomNo";
				//System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String roomNo, phoneNum, position, typeName;
				int principal, typeId, status, hourRoom, bedNum;
				double deposit, price;
				while (rs.next()) {
					roomNo = rs.getString("RoomNo").trim();
					phoneNum = rs.getString("PhoneNum");
					position = rs.getString("Position");
					typeName = rs.getString("TypeName");
					principal = rs.getInt("Principal");
					typeId = rs.getInt("TypeID");
					status = rs.getInt("Status");
					hourRoom = rs.getInt("HourRoom");
					bedNum = rs.getInt("BedNum");
					deposit = rs.getDouble("Deposit");
					price = rs.getDouble("Price");
					if(phoneNum != null)  phoneNum.trim(); else phoneNum="";
					if(position != null)  position.trim(); else position="";
					
					Room room = new Room(roomNo, status, phoneNum, position, principal, 
							typeId, typeName, hourRoom, bedNum, deposit, price);
					data.add(room);
				}
				stmt.close();
				/*System.out.println("数据库表Student已有数据" + data.size() + "条");
				for (int i = 0; i < data.size(); i++) {
				 	System.out.println(data.get(i).toString()); 
				 }*/
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomSql.closeAll();
		}

		return data;
	}
	
	// 获取房间信息
	public static Room get(String roomNo) {
		String sql;
		Room room = null;
		if (roomNo == null || roomNo.equals(""))
			return null;
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = String.format(
						"SELECT `Status`, PhoneNum, Position, Principal,"
								+ "TypeID, TypeName, HourRoom, BedNum, Deposit, Price FROM "
								+ "room_info ,room_type WHERE room_info.Type = room_type.TypeID WHERE RoomNo='%s'",
						roomNo);
				// System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String phoneNum, position, typeName;
				int principal, typeId, status, hourRoom, bedNum;
				double deposit, price;
				while (rs.next()) {
					//roomNo = rs.getString("RoomNo").trim();
					phoneNum = rs.getString("PhoneNum");
					position = rs.getString("Position");
					typeName = rs.getString("TypeName");
					principal = rs.getInt("Principal");
					typeId = rs.getInt("TypeID");
					status = rs.getInt("Status");
					hourRoom = rs.getInt("HourRoom");
					bedNum = rs.getInt("BedNum");
					deposit = rs.getDouble("Deposit");
					price = rs.getDouble("Price");
					if (phoneNum != null)
						phoneNum.trim();
					if (position != null)
						position.trim();

					room = new Room(roomNo, status, phoneNum, position, principal, typeId, typeName, hourRoom, bedNum,
							deposit, price);
				}
				stmt.close();
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomSql.closeAll();
		}
		return room;
	}
	
	// 添加房间信息
	public static Boolean add(Room room) {
		String sql;
		int ret = 0;
		if(room.getRoomNo() == null) return false;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("insert into room_info(RoomNo,Type,Status,PhoneNum,"+
						"Position,Principal) values('%s',%d,%d,'%s','%s',%d)",
						room.getRoomNo(), room.getTypeId(), room.getStatus(), 
						room.getPhoneNum(), room.getPosition(), room.getPrincipal());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 更新房间信息
	public static Boolean update(Room room) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("update room_info set Type=%d,Status=%d,PhoneNum='%s',"+
						"Position='%s',Principal='%s' where RoomNo='%s'",
						room.getTypeId(), room.getStatus(), room.getPhoneNum(), 
						room.getPosition(), room.getPrincipal(), room.getRoomNo());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 删除房间信息
	public static Boolean delete(Room room) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("delete from room_info where RoomNo='%s'", room.getRoomNo());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			RoomSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 条件获取房间信息
	public static ArrayList<Room> getCondSearch(String col, String cond) {
		String sqlCol = "";
		
		switch(col) {
		case "房间编号": case "编号":
			sqlCol = "RoomNo";
			break;
		case "房间类型": case "类型":
			sqlCol = "TypeName";
			break;
		case "房间电话": case "电话":
			sqlCol = "PhoneNum";
			break;
		case "房间位置": case "位置":
			sqlCol = "Position";
			break;
		case "钟点房":
			sqlCol = "HourRoom";
			if(cond.equals("是") || cond.equals("")) cond = "1";
			else if(cond.equals("否") || cond.equals("不是"))  cond = "0";
			break;
		case "床位数量":
			sqlCol = "BedNum";
			break;
		case "需交押金":case "押金":
			sqlCol = "Deposit";
			break;
		case "房间单价":
			sqlCol = "Price";
			break;
		case "员工编号": 
			sqlCol = "Principal";
			break;
		case "房间状态":
			sqlCol = "Status";
			if(cond.equals("可供")) cond="0";
			else if(cond.equals("停用")) cond="1";
			break;
		default:
			sqlCol = "";
				break;
		}
		
		String sql;
		ArrayList<Room> data = new ArrayList<Room>();
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				
				if (sqlCol == null || sqlCol.equals("")) {
					if (cond.equals("")) {
						data = getAll();
						return data;
					} else {
						sql = String.format("select * from room_info,room_type where "
								+"room_info.Type = room_type.TypeID and CONCAT(RoomNo,`Status`,"
								+ "ifnull(PhoneNum,''), ifnull(Position,''), ifnull(Principal,''), TypeID, TypeName, HourRoom, BedNum, "
								+ "Deposit, Price) like '%%%s%%' order by RoomNo", cond);
					}
				} else {
					sql = String.format("select * from room_info,room_type where "+
							"room_info.Type = room_type.TypeID and %s like '%%%s%%' order by RoomNo", sqlCol, cond);
				}
				//System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String roomNo, phoneNum, position, typeName;
				int principal, typeId, status, hourRoom, bedNum;
				double deposit, price;
				while (rs.next()) {
					roomNo = rs.getString("RoomNo").trim();
					phoneNum = rs.getString("PhoneNum");
					position = rs.getString("Position");
					typeName = rs.getString("TypeName");
					principal = rs.getInt("Principal");
					typeId = rs.getInt("TypeID");
					status = rs.getInt("Status");
					hourRoom = rs.getInt("HourRoom");
					bedNum = rs.getInt("BedNum");
					deposit = rs.getDouble("Deposit");
					price = rs.getDouble("Price");
					if (phoneNum != null)
						phoneNum.trim();
					if (position != null)
						position.trim();

					Room room = new Room(roomNo, status, phoneNum, position, principal, typeId, typeName, hourRoom,
							bedNum, deposit, price);
					data.add(room);
				}
				stmt.close();
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomSql.closeAll();
		}
		return data;
	}
	
	// 获取所有位置信息
	public static ArrayList<String> getAllPosition() {
		String sql;

		ArrayList<String> data = new ArrayList<String>();
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "select distinct position from room_info";
				// System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String position;
				while (rs.next()) {
					position = rs.getString("Position");
					if (position != null) {
						position.trim();
						if(!position.equals("")) {
							data.add(position);
						}
					}
				}
				stmt.close();
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomSql.closeAll();
		}
		return data;
	}

	private static void closeAll() {
		if (rs != null) try { rs.close(); } catch(Exception e) {}
	    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	    if (conn != null) try { conn.close(); } catch(Exception e) {}
	}
}
